# -*- coding: utf-8 -*-
# @Time    : 2019/10/08 19:11
# @Author  : ZSQ
# @Email   : zsq199170918@163.com
# @FileName: cq315_mongo_to_csv.py
# @Software: PyCharm

DISTRICT_LI = ['渝中', '江北', '南岸', '九龙坡', '沙坪坝', '大渡口', '北碚', '渝北', '巴南', '两江新', '北部新', '高新']
HOST = '192.168.1.159'
PORT = 27017

import re
import os
import sys
import time
import random
import logging
import functools
import threading
import pandas as pd
from copy import deepcopy
from pymongo import MongoClient
from datetime import datetime, timedelta

logger = logging.getLogger('cq315_logger')
logger.setLevel(logging.INFO)
# 日志输出到控制台
rf_handler = logging.StreamHandler(sys.stderr)  # 默认是sys.stderr
rf_handler.setLevel(logging.DEBUG)
rf_handler.setFormatter(logging.Formatter("%(asctime)s - %(name)s - %(message)s"))
# 日志记录到本地文件
today = datetime.now()
f_handler = logging.FileHandler('../logs/cq315_{}_{}_{}.log'.format(today.year, today.month, today.day))
f_handler.setLevel(logging.ERROR)
f_handler.setFormatter(logging.Formatter("%(asctime)s - %(levelname)s - %(filename)s[:%(lineno)d] - %(message)s"))

logger.addHandler(rf_handler)
logger.addHandler(f_handler)


def get_using_time(func):
    @functools.wraps(func)
    def wrapper(self, *args, **kwargs):
        start_time = datetime.now()
        print('=' * 60)
        print('>>>数据标准化开始<<<')
        ret = func(self, *args, **kwargs)
        delta = (datetime.now() - start_time).total_seconds()
        print('=' * 60)
        print('>>>数据标准化完成，共耗时 {} 秒<<<'.format(delta))
        return ret

    return wrapper


class ChongQing(object):
    def __init__(self, host, port):
        self.client = MongoClient(host=host, port=port, )
        self.flag = True

    def format_estate(self, db1, col1, db2, col2, query=None, limit_num=1000):
        """
        标准化楼盘表
        :param db1: 原数据库
        :param col1: 原楼盘表
        :param db2: 数据库
        :param col2: 楼盘表
        :param query: 查询条件
        :param limit_num: 每次查询个数
        :return:
        """
        clo1 = self.client[db1][col1]
        clo2 = self.client[db2][col2]
        skip_num = 0
        while True:
            item_li = list(clo1.find(query).skip(skip_num).limit(limit_num))
            if len(item_li) > 0:
                skip_num += 1000
                for item in item_li:
                    # 行政区划的添加逻辑
                    if not item.get('district', None):
                        location = item.get('location', None)
                        if not location:
                            cq_query = dict(
                                projectname=item['projectname'],
                            )
                            cq_item = dict(clo2.find_one(cq_query))
                            if cq_item:
                                item['district'] = cq_item['district']

                        for i in DISTRICT_LI:
                            if location and location.find(i) >= 0:
                                item['district'] = '{}区'.format(i)
                                break
                        item['district'] = item.get('district', None)

                    if item['district']:
                        item.pop('_id', '404 NOT FOUND')
                        item.pop('buildingid', '404 NOT FOUND')
                        item.pop('blockname', '404 NOT FOUND')
                        clo2.update_one(item, {'$set': item}, upsert=True)
                    else:
                        logger.error(
                            '{}-{}-{}  无法提取行政区划信息，不能进行标准化操作'.format(item['projectname'], item['certificate_time'],
                                                                    item['blockname']))
            else:
                break

    def format_building(self, db1, col1, db2, col2, query=None, limit_num=1000):
        """
        拆分出楼栋表（原数据库只有楼盘表和房号表）
        :param db1: 原数据库
        :param col1: 原楼盘表
        :param db2: 数据库
        :param col2: 楼栋表
        :param query: 查询条件
        :param limit_num: 每次查询个数
        :return:
        """
        clo1 = self.client[db1][col1]
        clo2 = self.client[db2][col2]
        skip_num = 0
        while True:
            item_li = list(clo1.find(query).skip(skip_num).limit(limit_num))
            if len(item_li) > 0:
                skip_num += 1000
                for item in item_li:
                    if not item.get('district', None):
                        location = item.get('location', None)
                        if not location:
                            cq_query = dict(
                                projectname=item['projectname'],
                            )
                            cq_item = dict(clo2.find_one(cq_query))
                            if cq_item:
                                item['district'] = cq_item['district']

                        for i in DISTRICT_LI:
                            if location and location.find(i) >= 0:
                                item['district'] = '{}区'.format(i)
                                break
                        item['district'] = item.get('district', None)

                    if item['district']:
                        bd_id_li = item['buildingid'].split(',') if item['buildingid'] else []
                        bn_id_li = item['blockname'].split(',') if item['blockname'] else []
                        if len(bd_id_li) == len(bn_id_li):
                            item.pop('_id', '404 NOT FOUND')
                            if len(bd_id_li) > 1:
                                for i, j in enumerate(bd_id_li):
                                    item['buildingid'] = j
                                    item['blockname'] = bn_id_li[i]
                                    clo2.update_one(item, {'$set': item}, upsert=True)
                            if len(bd_id_li) == 1:
                                clo2.update_one(item, {'$set': item}, upsert=True)
            else:
                break

    def format_room(self, db1, col1, db2, col2, query=None, limit_num=1000):
        """
        标准化房号表
        :param db1: 原数据库
        :param col1: 原房号表
        :param db2: 数据库
        :param col2: 房号表
        :param query: 查询条件
        :param limit_num: 每次查询个数
        :return:
        """
        clo1 = self.client[db1][col1]
        clo2 = self.client[db2][col2]
        clo3 = self.client['cqfc']['building']
        building_id_li = clo3.distinct('buildingid')
        skip_num = 0
        while True:
            # while skip_num < 2000:
            item_li = list(clo1.find(query).skip(skip_num).limit(limit_num))
            if len(item_li) > 0:
                skip_num += 1000
                for item in item_li:
                    item['buildingGuid'] = item.get('buildingGuid', None)
                    if item['buildingGuid'] and item['buildingGuid'] in building_id_li:
                        item['buildingid'] = item['buildingGuid']
                        item.pop('_id', '404 NOT FOUND')
                        item.pop('buildingGuid', '404 NOT FOUND')
                        clo2.update_one(item, {'$set': item}, upsert=True)
            else:
                break

    def get_recent_time(self, db, col):
        """
        获取数据最近一次标准化时间
        :param db: 数据库
        :param col: 楼盘表
        :return: 时间字符串      2019-07-29 02:47:00
        """
        col = self.client[db][col]
        sort_li = [('createtime', -1)]
        item = col.find_one(sort=sort_li)
        create_time = item['createtime']
        recent_time_date = datetime.strptime(create_time, '%Y-%m-%d %H:%M:%S').date() + timedelta(days=1)
        recent_time_str = recent_time_date.strftime('%Y-%m-%d %H:%M:%S')
        return recent_time_str

    @staticmethod
    def get_input_time():
        """
        获取开始和截止日期(手动输入)
        :return:
        """
        print('=' * 60)
        info_str = """请输入要导出的数据日期，日期格式如下：
        格式一： 2019/07/29
        格式二： 2019-07-29"""
        print(info_str)
        print('=' * 60)
        info_str_2 = """例如，获取2019年8月数据，请输入：
        2019/08/01 或 2019-08-01
        2019/09/01 或 2019-09-01"""
        print(info_str_2)
        print('=' * 60)
        info_str_3 = """Tips：\n1.输入的截止日期不在导出范围，如需导出，需把日期往后加一天\n2.例如：要导出2019年10月01日数据，截止日期应为: 2019-10-02"""
        print(info_str_3)
        print('=' * 60)
        time.sleep(1)
        while True:
            start_input = input('请输入开始日期:')
            end_input = input('请输入截止日期:')
            # start_input = ' 2019/09/01 '
            # end_input = ' 2019/10/01 '
            print('=' * 60)
            try:
                start_input = re.sub(r'/', '-', start_input.strip())
                end_input = re.sub(r'/', '-', end_input.strip())
                start_date = datetime.strptime(start_input, '%Y-%m-%d')
                end_date = datetime.strptime(end_input, '%Y-%m-%d')
                assert start_date <= end_date
            except Exception as e:
                print('=' * 60)
                print('>>>输入的日期有误，请重新输入<<<')
                print('=' * 60)
            else:
                print('>>>输入成功，数据正在导出，请稍等...<<<')
                return start_date, end_date

    @staticmethod
    def remove_file_exist(_file):
        if os.path.exists(_file):
            os.remove(_file)

    def show_msg(self):
        count = 0
        while True:
            count += 1
            time.sleep(10)
            if self.flag:
                print('>>>数据正在标准化，请稍等...({}s)<<<'.format(count * 10))
            else:
                break

    @get_using_time
    def run(self, city_name):
        t = threading.Thread(target=self.show_msg, daemon=True)
        t.start()
        time_str = self.get_recent_time('cqfc', 'estate')
        query_dict = {
            'city': city_name,
            'createtime': {'$gt': time_str},
        }
        rm_query_dict = {
            'city': city_name,
            'createTime': {'$gt': time_str},
        }
        # 楼盘
        # self.format_estate('cbrdb', 'original_community', 'cqfc', 'estate', query=query_dict)  # 只有第一次标准化数据才需要执行
        self.format_estate('cbrdb', 'temp', 'cqfc', 'estate', query=query_dict)
        # 楼栋
        # self.format_building('cbrdb', 'original_community', 'cqfc', 'building', query=query_dict)  # 只有第一次标准化数据才需要执行
        self.format_building('cbrdb', 'temp', 'cqfc', 'building', query=query_dict)
        # 房号
        self.format_room('cbrdb', 'room', 'cqfc', 'room', query=rm_query_dict)

        self.flag = False

    def find_to_csv(self):
        """
        按 旷部长 需求导出数据(csv格式)
        :return:
        """
        item_temp = dict(
            city='重庆市',
            district='',
            project_name='',
            buildings_num=0,
            room_use='',
            rooms_num=0,
            avg_price=0,
            price=[],
        )
        item_li = []
        flag = True
        col1 = self.client['cqfc']['building']
        col2 = self.client['cqfc']['room']
        bd_pipeline = [
            {'$group': {
                '_id': {
                    'projectname': '$projectname',
                    'district': '$district',
                    'city': '重庆市',
                },
                'count': {'$sum': 1},
                'dups': {'$addToSet': '$buildingid'}
            }},
            # {'$match': {'count': {'$gt': 1}}},
        ]
        bd_dups_cursor = col1.aggregate(bd_pipeline, allowDiskUse=True)
        bd_dups_li = list(bd_dups_cursor)
        for bd in bd_dups_li:
            item = item_temp
            item['district'] = bd['_id']['district']
            item['project_name'] = bd['_id']['projectname']
            item['buildings_num'] = bd['count']
            bd_id_li = bd['dups']

            room_use_li = col2.distinct('roomUse')
            room_use_li = [i for i in room_use_li if '住宅' in i]
            rm_pipeline = [
                {'$group': {
                    '_id': {
                        'buildingid': '$buildingid',
                        'roomUse': '$roomUse',
                    },
                    'count': {'$sum': 1},
                    'dups': {'$addToSet': '$price'}
                }},
            ]
            rm_dups_cursor = col2.aggregate(rm_pipeline, allowDiskUse=True)
            rm_dups_li = list(rm_dups_cursor)
            for room_use in room_use_li:
                item_rm = deepcopy(item)
                item_rm['room_use'] = room_use
                for rm in rm_dups_li:
                    if rm['_id']['buildingid'] in bd_id_li and rm['_id']['roomUse'] == item_rm['room_use']:
                        item_rm['rooms_num'] += int(rm['count'])
                        item_rm['price'] += rm['dups']
                item_rm['avg_price'] = round(sum(item_rm['price']) / len(item_rm['price']), 2) if len(
                    item_rm['price']) > 0 else None
                item_rm['price'] = list(set(item_rm['price']))
                item_rm['price'].sort()
                item_rm['price'] = re.sub(r'[[]|[]]', '', str(item_rm['price']))
                item_li.append(item_rm)
                # 逐条写入csv
                item_rm_li = [item_rm]
                df = pd.DataFrame(item_rm_li)
                if flag:
                    flag = False
                    df.to_csv('../csv_file/重庆楼盘价格.csv', index=None, mode='a', encoding='gbk')
                else:
                    df.to_csv('../csv_file/重庆楼盘价格.csv', index=None, header=None, mode='a', encoding='gbk')
        # 一次写入csv
        # df = pd.DataFrame(item_li)
        # df.to_csv('123.csv', index=None, encoding='gbk')

    def find_to_csv_2(self):
        """
        按 方总 需求导出数据(csv格式)
        :return:
        """
        start_time = datetime.now()
        item_temp = dict(
            certDate='',
            projectName='',
            preSalePermit='',
            districtName='',
            blockName='',
            buildingId='',
            # saleableRoomNum='',
            roomUse='',
            totalRoomNum='',
            totalFloor='',
            propertyState='',
            roomNo='',
            roomUnit='',
            roomFloor='',
            innerArea='',
            roomType='',
            unitPrice='',
        )
        flag = True
        col1 = self.client['cqfc']['building']
        col2 = self.client['cqfc']['room']
        # 获取起止日期
        start_date, end_date = self.get_input_time()
        start_m = start_date.month
        start_d = start_date.day
        file_end_date = end_date - timedelta(days=1)
        end_m = file_end_date.month
        end_d = file_end_date.day
        file_path = '../csv_file/cq315住宅_{}月{}日_{}月{}日.csv'.format(start_m, start_d, end_m, end_d)
        self.remove_file_exist(file_path)
        while start_date < end_date:
            certificate_time = datetime.strftime(start_date, '%Y-%m-%d')
            datetime_temp = start_date
            bd_cursor = col1.find(dict(certificate_time=certificate_time))
            bd_li = list(bd_cursor)
            if bd_li:
                for bd in bd_li:
                    item = deepcopy(item_temp)
                    item['certDate'] = bd.get('certificate_time', None)
                    item['projectName'] = bd.get('projectname', None)
                    item['preSalePermit'] = bd.get('f_presale_cert', None)
                    item['districtName'] = bd.get('district', None)
                    item['blockName'] = bd.get('blockname', None)
                    item['buildingId'] = bd.get('buildingid', None)

                    # 获取楼栋总楼层
                    rm_pipeline = [
                        {'$group': {
                            '_id': {
                                'buildingid': '$buildingid',
                            },
                            'dups': {'$addToSet': '$floor'}
                        }},
                        {'$match': {'_id.buildingid': item['buildingId'], }},
                    ]
                    rm_dups_cursor = col2.aggregate(rm_pipeline, allowDiskUse=True)
                    rm_dups_li = list(rm_dups_cursor)
                    try:
                        floor_li = list(rm_dups_li[0]['dups'])
                        floor_li = [int(re.findall(r'\d+', i)[0]) for i in floor_li if len(re.findall(r'\d+', i))]
                        floor_li.sort()
                    except:
                        logger.error(
                            '{}-{}-{} 总楼层获取出错'.format(item['certDate'], item['projectName'], item['buildingId']))
                        floor_li = list()
                    # 判断建筑类别
                    item['totalFloor'] = floor_li[-1] if len(floor_li) else None
                    if item['totalFloor']:
                        max_floor = int(item['totalFloor'])
                        if max_floor >= 35:
                            item['propertyState'] = '超高层'
                        elif max_floor >= 19:
                            item['propertyState'] = '高层'
                        elif max_floor >= 8:
                            item['propertyState'] = '小高层'
                        elif max_floor >= 4:
                            item['propertyState'] = '多层'
                        else:
                            item['propertyState'] = '低层'
                    else:
                        item['propertyState'] = '其它'

                    # 获取楼栋可售总套数（住宅）
                    # PS:因原程序中房号销售状态功能未实现，故 2019/10/15前 无法区分 可售/不可售
                    room_use_li = col2.distinct('roomUse')
                    room_use_li = [i for i in room_use_li if '住宅' in i]
                    creat_time_start = datetime_temp + timedelta(days=1)
                    creat_time_start = creat_time_start.strftime('%Y-%m-%d %H:%M:%S')
                    creat_time_end = datetime_temp + timedelta(days=30)
                    creat_time_end = creat_time_end.strftime('%Y-%m-%d %H:%M:%S')
                    for room_use in room_use_li:
                        item_rm = deepcopy(item)
                        if creat_time_start >= '2019-10-15 00:00:00':
                            query_dict = {
                                'buildingid': item['buildingId'],
                                'roomUse': room_use,
                                'roomStatus': '可售',
                                'price': {'$ne': 0},
                                '$and': [{'createTime': {'$gt': creat_time_start}},
                                         {'createTime': {'$lt': creat_time_end}}]
                            }
                        else:
                            query_dict = {
                                'buildingid': item['buildingId'],
                                'roomUse': room_use,
                                '$and': [{'createTime': {'$gt': creat_time_start}},
                                         {'createTime': {'$lt': creat_time_end}}]
                            }
                        rm_cursor = col2.find(query_dict)
                        rm_li = list(rm_cursor)
                        if len(rm_li):
                            room = random.choice(rm_li)
                            item_rm['totalRoomNum'] = len(rm_li)
                            item_rm['roomUse'] = room_use
                            item_rm['roomUnit'] = room.get('unit', None)
                            item_rm['roomFloor'] = room.get('floor', None)
                            # floor_num_li = re.findall(r'\d+', item_rm['roomFloor'])
                            # if len(floor_num_li):
                            #     floor_num = int(floor_num_li[0])
                            #     if floor_num >= 12:
                            #         item_rm['propertyState'] = '高层'
                            #     elif floor_num >= 7:
                            #         item_rm['propertyState'] = '小高层'
                            #     elif floor_num >= 4:
                            #         item_rm['propertyState'] = '多层'
                            #     else:
                            #         item_rm['propertyState'] = '低层'
                            # else:
                            #     item_rm['propertyState'] = None
                            item_rm['roomNo'] = '{}号'.format(room.get('roomName', None))
                            item_rm['innerArea'] = room.get('insideArea', None)
                            item_rm['roomType'] = room.get('roomType', None)
                            item_rm['unitPrice'] = room.get('price', None)

                            item_rm.pop('buildingId')
                            item_rm.pop('totalFloor')
                            item_rm.pop('roomFloor')

                            # 逐条写入csv
                            item_rm_li = [item_rm]
                            df = pd.DataFrame(item_rm_li)
                            if flag:
                                flag = False
                                df.to_csv(file_path, index=None, mode='a', encoding='gbk')
                            else:
                                df.to_csv(file_path, index=None, header=None, mode='a', encoding='gbk')
                print('>>>{}-{}-{} 数据导出完成<<<'.format(start_date.year, start_date.month, start_date.day))
            start_date += timedelta(days=1)
        print('=' * 60)
        delta = (datetime.now() - start_time).total_seconds()
        print('>>>{}/{}-{}/{} 数据导出完成，共耗时{}秒<<<'.format(start_m, start_d, end_m, end_d, delta))
        print('=' * 60)

    def find_to_csv_3(self):
        """
        按 方总 需求导出数据(csv格式)
        <包括住宅和非住宅的所有新增楼盘>
        :return:
        """
        start_time = datetime.now()
        item_temp = dict(
            certDate='',
            projectName='',
            preSalePermit='',
            districtName='',
            blockName='',
            buildingId='',
            # saleableRoomNum='',
            roomUse='',
            totalRoomNum='',
            totalFloor='',
            propertyState='',
            roomNo='',
            roomUnit='',
            roomFloor='',
            innerArea='',
            roomType='',
            unitPrice='',
        )
        flag = True
        col1 = self.client['cqfc']['building']
        col2 = self.client['cqfc']['room']
        # 获取起止日期
        start_date, end_date = self.get_input_time()
        start_m = start_date.month
        start_d = start_date.day
        file_end_date = end_date - timedelta(days=1)
        end_m = file_end_date.month
        end_d = file_end_date.day
        file_path = '../csv_file/cq315_{}月{}日_{}月{}日.csv'.format(start_m, start_d, end_m, end_d)
        self.remove_file_exist(file_path)
        while start_date < end_date:
            certificate_time = datetime.strftime(start_date, '%Y-%m-%d')
            datetime_temp = start_date
            bd_cursor = col1.find(dict(certificate_time=certificate_time))
            bd_li = list(bd_cursor)
            if bd_li:
                for bd in bd_li:
                    item = deepcopy(item_temp)
                    item['certDate'] = bd.get('certificate_time', None)
                    item['projectName'] = bd.get('projectname', None)
                    item['preSalePermit'] = bd.get('f_presale_cert', None)
                    item['districtName'] = bd.get('district', None)
                    item['blockName'] = bd.get('blockname', None)
                    item['buildingId'] = bd.get('buildingid', None)

                    # 获取楼栋总楼层
                    rm_pipeline = [
                        {'$group': {
                            '_id': {
                                'buildingid': '$buildingid',
                            },
                            'dups': {'$addToSet': '$floor'}
                        }},
                        {'$match': {'_id.buildingid': item['buildingId'], }},
                    ]
                    rm_dups_cursor = col2.aggregate(rm_pipeline, allowDiskUse=True)
                    rm_dups_li = list(rm_dups_cursor)
                    try:
                        floor_li = list(rm_dups_li[0]['dups'])
                        floor_li = [int(re.findall(r'\d+', i)[0]) for i in floor_li if len(re.findall(r'\d+', i))]
                        floor_li.sort()
                    except:
                        logger.error(
                            '{}-{}-{} 总楼层获取出错'.format(item['certDate'], item['projectName'], item['buildingId']))
                        floor_li = list()
                    # 判断建筑类别
                    item['totalFloor'] = floor_li[-1] if len(floor_li) else None
                    if item['totalFloor']:
                        max_floor = int(item['totalFloor'])
                        if max_floor >= 35:
                            item['propertyState'] = '超高层'
                        elif max_floor >= 19:
                            item['propertyState'] = '高层'
                        elif max_floor >= 8:
                            item['propertyState'] = '小高层'
                        elif max_floor >= 4:
                            item['propertyState'] = '多层'
                        else:
                            item['propertyState'] = '低层'
                    else:
                        item['propertyState'] = '其它'

                    # 获取楼栋可售总套数（住宅和非住宅）
                    # PS:因原程序中房号销售状态功能未实现，故 2019/10/15前 无法区分 可售/不可售
                    room_use_li = col2.distinct('roomUse')
                    # room_use_li = [i for i in room_use_li if '住宅' in i]
                    room_use_li = [i for i in room_use_li if i]
                    creat_time_start = datetime_temp + timedelta(days=1)
                    creat_time_start = creat_time_start.strftime('%Y-%m-%d %H:%M:%S')
                    creat_time_end = datetime_temp + timedelta(days=30)
                    creat_time_end = creat_time_end.strftime('%Y-%m-%d %H:%M:%S')
                    for room_use in room_use_li:
                        item_rm = deepcopy(item)
                        if creat_time_start >= '2019-10-15 00:00:00':
                            query_dict = {
                                'buildingid': item['buildingId'],
                                'roomUse': room_use,
                                'roomStatus': '可售',
                                'price': {'$ne': 0},
                                '$and': [{'createTime': {'$gt': creat_time_start}},
                                         {'createTime': {'$lt': creat_time_end}}]
                            }
                        else:
                            query_dict = {
                                'buildingid': item['buildingId'],
                                'roomUse': room_use,
                                '$and': [{'createTime': {'$gt': creat_time_start}},
                                         {'createTime': {'$lt': creat_time_end}}]
                            }
                        rm_cursor = col2.find(query_dict)
                        rm_li = list(rm_cursor)
                        if len(rm_li):
                            room = random.choice(rm_li)
                            item_rm['totalRoomNum'] = len(rm_li)
                            item_rm['roomUse'] = room_use
                            item_rm['roomUnit'] = room.get('unit', None)
                            item_rm['roomFloor'] = room.get('floor', None)
                            # floor_num_li = re.findall(r'\d+', item_rm['roomFloor'])
                            # if len(floor_num_li):
                            #     floor_num = int(floor_num_li[0])
                            #     if floor_num >= 12:
                            #         item_rm['propertyState'] = '高层'
                            #     elif floor_num >= 7:
                            #         item_rm['propertyState'] = '小高层'
                            #     elif floor_num >= 4:
                            #         item_rm['propertyState'] = '多层'
                            #     else:
                            #         item_rm['propertyState'] = '低层'
                            # else:
                            #     item_rm['propertyState'] = None
                            item_rm['roomNo'] = '{}号'.format(room.get('roomName', None))
                            item_rm['innerArea'] = room.get('insideArea', None)
                            item_rm['roomType'] = room.get('roomType', None)
                            item_rm['unitPrice'] = room.get('price', None)

                            # 获取楼栋总楼层
                            # rm_pipeline = [
                            #     {'$group': {
                            #         '_id': {
                            #             'buildingid': '$buildingid',
                            #             'roomUse': '$roomUse',
                            #         },
                            #         'dups': {'$addToSet': '$floor'}
                            #     }},
                            #     {'$match': {'_id.buildingid': item['buildingId'], '_id.roomUse': item_rm['roomUse']}},
                            # ]
                            # rm_dups_cursor = col2.aggregate(rm_pipeline, allowDiskUse=True)
                            # rm_dups_li = list(rm_dups_cursor)
                            # floor_li = list(rm_dups_li[0]['dups'])
                            # floor_li = [int(re.findall(r'\d+', i)[0]) for i in floor_li if len(re.findall(r'\d+', i))]
                            # floor_li.sort()
                            # item_rm['totalFloor'] = floor_li[-1] if len(floor_li) else None

                            item_rm.pop('buildingId')
                            item_rm.pop('totalFloor')
                            item_rm.pop('roomFloor')

                            # 逐条写入csv
                            item_rm_li = [item_rm]
                            df = pd.DataFrame(item_rm_li)
                            if flag:
                                flag = False
                                df.to_csv(file_path, index=None, mode='a', encoding='gbk')
                            else:
                                df.to_csv(file_path, index=None, header=None, mode='a', encoding='gbk')
                print('>>>{}-{}-{} 数据导出完成<<<'.format(start_date.year, start_date.month, start_date.day))
            start_date += timedelta(days=1)
        print('=' * 60)
        delta = (datetime.now() - start_time).total_seconds()
        print('>>>{}/{}-{}/{} 数据导出完成，共耗时{}秒<<<'.format(start_m, start_d, end_m, end_d, delta))
        print('=' * 60)


if __name__ == '__main__':
    cq = ChongQing(HOST, PORT)
    cq.run('重庆市')  # 标准化原数据
    # cq.find_to_csv()  # 旷部长
    # cq.find_to_csv_2()  # 方总  住宅
    # cq.find_to_csv_3()  # 方总  住宅&非住宅

    # now = datetime.strptime(ret, '%Y-%m-%d %H:%M:%S')
    # now += timedelta(days=1)
    # now = now.strftime('%Y-%m-%d %H:%M:%S')
    # print(now)
    # print(type(now))
    # now_date = now.date() + timedelta(days=1)
    # now_str = now_date.strftime('%Y-%m-%d %H:%M:%S')
    pass
